home *** CD-ROM | disk | FTP | other *** search
/ SGI Freeware 2002 November / SGI Freeware 2002 November - Disc 3.iso / dist / fw_mysql.idb / usr / freeware / share / sql-bench / test-select.z / test-select
Text File  |  2002-10-07  |  14KB  |  439 lines
  1. #!/usr/sbin/perl
  2. # Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
  3. #
  4. # This library is free software; you can redistribute it and/or
  5. # modify it under the terms of the GNU Library General Public
  6. # License as published by the Free Software Foundation; either
  7. # version 2 of the License, or (at your option) any later version.
  8. #
  9. # This library is distributed in the hope that it will be useful,
  10. # but WITHOUT ANY WARRANTY; without even the implied warranty of
  11. # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
  12. # Library General Public License for more details.
  13. #
  14. # You should have received a copy of the GNU Library General Public
  15. # License along with this library; if not, write to the Free
  16. # Software Foundation, Inc., 59 Temple Place - Suite 330, Boston,
  17. # MA 02111-1307, USA
  18. #
  19. # Test of selecting on keys that consist of many parts
  20. #
  21. ##################### Standard benchmark inits ##############################
  22.  
  23. use DBI;
  24. use Getopt::Long;
  25. use Benchmark;
  26.  
  27. $opt_loop_count=10000;
  28. $opt_medium_loop_count=1000;
  29. $opt_small_loop_count=10;
  30. $opt_regions=6;
  31. $opt_groups=100;
  32.  
  33. chomp($pwd = `pwd`); $pwd = "." if ($pwd eq '');
  34. require "$pwd/bench-init.pl" || die "Can't read Configuration file: $!\n";
  35.  
  36. $columns=min($limits->{'max_columns'},500,($limits->{'query_size'}-50)/24,
  37.          $limits->{'max_conditions'}/2-3);
  38.  
  39. if ($opt_small_test)
  40. {
  41.   $opt_loop_count/=10;
  42.   $opt_medium_loop_count/=10;
  43.   $opt_small_loop_count/=10;
  44.   $opt_groups/=10;
  45. }
  46.  
  47. print "Testing the speed of selecting on keys that consist of many parts\n";
  48. print "The test-table has $opt_loop_count rows and the test is done with $columns ranges.\n\n";
  49.  
  50. ####
  51. ####  Connect and start timeing
  52. ####
  53.  
  54. $dbh = $server->connect();
  55. $start_time=new Benchmark;
  56.  
  57. ####
  58. #### Create needed tables
  59. ####
  60.  
  61. goto select_test if ($opt_skip_create);
  62.  
  63. print "Creating table\n";
  64. $dbh->do("drop table bench1" . $server->{'drop_attr'});
  65.  
  66. do_many($dbh,$server->create("bench1",
  67.                  ["region char(1) NOT NULL",
  68.                   "idn integer(6) NOT NULL",
  69.                   "rev_idn integer(6) NOT NULL",
  70.                   "grp integer(6) NOT NULL"],
  71.                  ["primary key (region,idn)",
  72.                   "unique (region,rev_idn)",
  73.                   "unique (region,grp,idn)"]));
  74. if ($opt_lock_tables)
  75. {
  76.   do_query($dbh,"LOCK TABLES bench1 WRITE");
  77. }
  78.  
  79. if ($opt_fast && defined($server->{vacuum}))
  80. {
  81.   $server->vacuum(1,\$dbh);
  82. }
  83.  
  84. ####
  85. #### Insert $opt_loop_count records with
  86. #### region:    "A" -> "E"
  87. #### idn:     0 -> count
  88. #### rev_idn:    count -> 0,
  89. #### grp:    distributed values 0 - > count/100
  90. ####
  91.  
  92. print "Inserting $opt_loop_count rows\n";
  93.  
  94. $loop_time=new Benchmark;
  95. $query="insert into bench1 values (";
  96. $half_done=$opt_loop_count/2;
  97. for ($id=0,$rev_id=$opt_loop_count-1 ; $id < $opt_loop_count ; $id++,$rev_id--)
  98. {
  99.   $grp=$id*3 % $opt_groups;
  100.   $region=chr(65+$id%$opt_regions);
  101.   do_query($dbh,"$query'$region',$id,$rev_id,$grp)");
  102.   if ($id == $half_done)
  103.   {                # Test with different insert
  104.     $query="insert into bench1 (region,idn,rev_idn,grp) values (";
  105.   }
  106. }
  107.  
  108. $end_time=new Benchmark;
  109. print "Time to insert ($opt_loop_count): " .
  110.     timestr(timediff($end_time, $loop_time),"all") . "\n\n";
  111.  
  112. if ($opt_lock_tables)
  113. {
  114.   do_query($dbh,"UNLOCK TABLES");
  115. }
  116.  
  117. if ($opt_fast && defined($server->{vacuum}))
  118. {
  119.   $server->vacuum(0,\$dbh,"bench1");
  120. }
  121.  
  122. if ($opt_lock_tables)
  123. {
  124.   do_query($dbh,"LOCK TABLES bench1 WRITE");
  125. }
  126.  
  127. ####
  128. #### Do some selects on the table
  129. ####
  130.  
  131. select_test:
  132.  
  133. if ($limits->{'group_functions'})
  134. {
  135.   my ($tmp); $tmp=1000;
  136.   print "Test if the database has a query cache\n";
  137.  
  138.   # First ensure that the table is read into memory
  139.   fetch_all_rows($dbh,"select sum(idn+$tmp),sum(rev_idn-$tmp) from bench1");
  140.  
  141.   $loop_time=new Benchmark;
  142.   for ($tests=0 ; $tests < $opt_loop_count ; $tests++)
  143.   {
  144.     fetch_all_rows($dbh,"select sum(idn+$tests),sum(rev_idn-$tests) from bench1");
  145.   }
  146.   $end_time=new Benchmark;
  147.   print "Time for select_query_cache ($opt_loop_count): " .
  148.      timestr(timediff($end_time, $loop_time),"all") . "\n\n";
  149.  
  150.   # If the database has a query cache, the following loop should be much
  151.   # slower than the previous loop
  152.  
  153.   $loop_time=new Benchmark;
  154.   for ($tests=0 ; $tests < $opt_loop_count ; $tests++)
  155.   {
  156.     fetch_all_rows($dbh,"select sum(idn+$tests),sum(rev_idn-$tests) from bench1");
  157.   }
  158.   $end_time=new Benchmark;
  159.   print "Time for select_query_cache2 ($opt_loop_count): " .
  160.      timestr(timediff($end_time, $loop_time),"all") . "\n\n";
  161. }
  162.  
  163.  
  164. print "Testing big selects on the table\n";
  165. $loop_time=new Benchmark;
  166. $rows=0;
  167. for ($i=0 ; $i < $opt_small_loop_count ; $i++)
  168. {
  169.   $grp=$i*11 % $opt_groups;
  170.   $region=chr(65+$i%($opt_regions+1));    # One larger to test misses
  171.   $rows+=fetch_all_rows($dbh,"select idn from bench1 where region='$region'");
  172.   $rows+=fetch_all_rows($dbh,"select idn from bench1 where region='$region' and idn=$i");
  173.   $rows+=fetch_all_rows($dbh,"select idn from bench1 where region='$region' and rev_idn=$i");
  174.   $rows+=fetch_all_rows($dbh,"select idn from bench1 where region='$region' and grp=$grp");
  175.   $rows+=fetch_all_rows($dbh,"select idn from bench1 where region>='B' and region<='C' and grp=$grp");
  176.   $rows+=fetch_all_rows($dbh,"select idn from bench1 where region>='B' and region<='E' and grp=$grp");
  177.   $rows+=fetch_all_rows($dbh,"select idn from bench1 where grp=$grp"); # This is hard
  178. }
  179. $count=$opt_small_loop_count*7;
  180.  
  181. $end_time=new Benchmark;
  182. print "Time for select_big ($count:$rows): " .
  183.     timestr(timediff($end_time, $loop_time),"all") . "\n";
  184.  
  185. # Test select with many OR's
  186.  
  187. $loop_time=new Benchmark;
  188. $tmpvar=0;
  189. $count=0;
  190. $estimated=0;
  191. $max_and_conditions=$limits->{'max_conditions'}/2;
  192. $rows=0;
  193.  
  194. for ($i=0 ; $i < $opt_small_loop_count ; $i++)
  195. {
  196.   $region=chr(65+$i%($opt_regions+1));    # One larger to test out-of-regions
  197.   $query="select * from bench1 where ";
  198.   $or_part="grp = 1";
  199.   $or_part2="region='A' and grp=1";
  200.  
  201.   for ($j=1 ; $j < $columns; $j++)
  202.   {
  203.     $tmpvar^= ((($tmpvar + 63) + $j)*3 % 100000);
  204.     $tmp=$tmpvar % $opt_groups;
  205.     $tmp_region=chr(65+$tmpvar%$opt_regions);
  206.     $or_part.=" or grp=$tmp";
  207.     if ($j < $max_and_conditions)
  208.     {
  209.       $or_part2.=" or region='$tmp_region' and grp=$tmp";
  210.     }
  211.   }
  212.   $or_part="region='$region' and ($or_part)";
  213.  
  214. # Same query, but use 'func_extra_in_num' instead.
  215.   if ($limits->{'func_extra_in_num'})
  216.   {
  217.     $in_part=$or_part;
  218.     $in_part=~ s/ = / IN \(/;
  219.     $in_part=~ s/ or grp=/,/g;
  220.     $in_part.= ")";
  221.     defined($found=fetch_all_rows($dbh,$query . $in_part)) || die $DBI::errstr;
  222.     $rows+=$found;
  223.     $count++;
  224.   }
  225.   for ($j=0; $j < 10 ; $j++)
  226.   {
  227.     $rows+=fetch_all_rows($dbh,$query . $or_part);
  228.     $rows+=fetch_all_rows($dbh,$query . $or_part2);
  229. # Do it a little harder by setting a extra range
  230.     $rows+=fetch_all_rows($dbh,"$query ($or_part) and idn < 50");
  231.     $rows+=fetch_all_rows($dbh,"$query (($or_part) or (region='A' and grp < 10)) and region <='B'")
  232.   }
  233.   $count+=$j*4;
  234.   $end_time=new Benchmark;
  235.   last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i+1,
  236.                      $opt_small_loop_count));
  237. }
  238.  
  239. print_time($estimated);
  240. print " for select_range ($count:$rows): " .
  241.   timestr(timediff($end_time, $loop_time),"all") . "\n";
  242.  
  243. #
  244. # Testing MIN() and MAX() on keys
  245. #
  246.  
  247. if ($limits->{'group_functions'} && $limits->{'order_by_unused'})
  248. {
  249.   $loop_time=new Benchmark;
  250.   $count=0;
  251.   $estimated=0;
  252.   for ($tests=0 ; $tests < $opt_loop_count ; $tests++)
  253.   {
  254.     $count+=7;
  255.     $grp=$tests*3 % $opt_groups;
  256.     $region=chr(65+$tests % $opt_regions);
  257.     if ($limits->{'group_func_sql_min_str'})
  258.     {
  259.       fetch_all_rows($dbh,"select min(region) from bench1");
  260.       fetch_all_rows($dbh,"select max(region) from bench1");
  261.       fetch_all_rows($dbh,"select min(region),max(region) from bench1");
  262.     }
  263.     fetch_all_rows($dbh,"select min(rev_idn) from bench1 where region='$region'");
  264.  
  265.     fetch_all_rows($dbh,"select max(grp) from bench1 where region='$region'");
  266.     fetch_all_rows($dbh,"select max(idn) from bench1 where region='$region' and grp=$grp");
  267.     if ($limits->{'group_func_sql_min_str'})
  268.     {
  269.       fetch_all_rows($dbh,"select max(region) from bench1 where region<'$region'");
  270.     }
  271.     $end_time=new Benchmark;
  272.     last if ($estimated=predict_query_time($loop_time,$end_time,\$count,
  273.                        $tests+1, $opt_loop_count));
  274.   }
  275.   print_time($estimated);
  276.   print " for min_max_on_key ($count): " .
  277.     timestr(timediff($end_time, $loop_time),"all") . "\n";
  278.  
  279.   $loop_time=new Benchmark;
  280.   $count=0;
  281.   $estimated=0;
  282.   for ($tests=0 ; $tests < $opt_loop_count ; $tests++)
  283.   {
  284.     $count+=5;
  285.     $grp=$tests*3 % $opt_groups;
  286.     $region=chr(65+$tests % $opt_regions);
  287.     fetch_all_rows($dbh,"select count(*) from bench1 where region='$region'");
  288.     fetch_all_rows($dbh,"select count(*) from bench1 where region='$region' and grp=$grp");
  289.     fetch_all_rows($dbh,"select count(*) from bench1 where region>'$region'");
  290.     fetch_all_rows($dbh,"select count(*) from bench1 where region<='$region'");
  291.     fetch_all_rows($dbh,"select count(*) from bench1 where region='$region' and grp>$grp");
  292.     $end_time=new Benchmark;
  293.     last if ($estimated=predict_query_time($loop_time,$end_time,\$count,
  294.                        $tests+1, $opt_loop_count));
  295.   }
  296.   print_time($estimated);
  297.   print " for count_on_key ($count): " .
  298.     timestr(timediff($end_time, $loop_time),"all") . "\n\n";
  299.   
  300. }
  301.  
  302. if ($limits->{'group_functions'})
  303. {
  304.   $loop_time=new Benchmark;
  305.   $rows=0;
  306.   for ($i=0 ; $i < $opt_medium_loop_count ; $i++)
  307.   {
  308.     $rows+=fetch_all_rows($dbh,"select grp,count(*) from bench1 group by grp");
  309.   }
  310.   $end_time=new Benchmark;
  311.   print "Time for count_group_on_key_parts ($i:$rows): " .
  312.     timestr(timediff($end_time, $loop_time),"all") . "\n";
  313. }
  314.  
  315. if ($limits->{'group_distinct_functions'})
  316. {
  317.   print "Testing count(distinct) on the table\n";
  318.   $loop_time=new Benchmark;
  319.   $rows=$estimated=$count=0;
  320.   for ($i=0 ; $i < $opt_medium_loop_count ; $i++)
  321.   {
  322.     $count++;
  323.     $rows+=fetch_all_rows($dbh,"select count(distinct region) from bench1");
  324.     $end_time=new Benchmark;
  325.     last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i+1,
  326.                        $opt_medium_loop_count));
  327.   }
  328.   print_time($estimated);
  329.   print " for count_distinct_key_prefix ($count:$rows): " .
  330.     timestr(timediff($end_time, $loop_time),"all") . "\n";
  331.  
  332.   $loop_time=new Benchmark;
  333.   $rows=$estimated=$count=0;
  334.   for ($i=0 ; $i < $opt_medium_loop_count ; $i++)
  335.   {
  336.     $count++;
  337.     $rows+=fetch_all_rows($dbh,"select count(distinct grp) from bench1");
  338.     $end_time=new Benchmark;
  339.     last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i+1,
  340.                        $opt_medium_loop_count));
  341.   }
  342.   print_time($estimated);
  343.   print " for count_distinct ($count:$rows): " .
  344.     timestr(timediff($end_time, $loop_time),"all") . "\n";
  345.  
  346.   $loop_time=new Benchmark;
  347.   $rows=$estimated=$count=0;
  348.   for ($i=0 ; $i < $opt_medium_loop_count ; $i++)
  349.   {
  350.     $count++;
  351.     $rows+=fetch_all_rows($dbh,"select count(distinct grp),count(distinct rev_idn) from bench1");
  352.     $end_time=new Benchmark;
  353.     last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i+1,
  354.                        $opt_medium_loop_count));
  355.   }
  356.   print_time($estimated);
  357.   print " for count_distinct_2 ($count:$rows): " .
  358.     timestr(timediff($end_time, $loop_time),"all") . "\n";
  359.  
  360.   $loop_time=new Benchmark;
  361.   $rows=$estimated=$count=0;
  362.   for ($i=0 ; $i < $opt_medium_loop_count ; $i++)
  363.   {
  364.     $count++;
  365.     $rows+=fetch_all_rows($dbh,"select region,count(distinct idn) from bench1 group by region");
  366.     $end_time=new Benchmark;
  367.     last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i+1,
  368.                        $opt_medium_loop_count));
  369.   }
  370.   print_time($estimated);
  371.   print " for count_distinct_group_on_key ($count:$rows): " .
  372.     timestr(timediff($end_time, $loop_time),"all") . "\n";
  373.  
  374.   $loop_time=new Benchmark;
  375.   $rows=$estimated=$count=0;
  376.   for ($i=0 ; $i < $opt_medium_loop_count ; $i++)
  377.   {
  378.     $count++;
  379.     $rows+=fetch_all_rows($dbh,"select grp,count(distinct idn) from bench1 group by grp");
  380.     $end_time=new Benchmark;
  381.     last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i+1,
  382.                        $opt_medium_loop_count));
  383.   }
  384.   print_time($estimated);
  385.   print " for count_distinct_group_on_key_parts ($count:$rows): " .
  386.     timestr(timediff($end_time, $loop_time),"all") . "\n";
  387.  
  388.   $loop_time=new Benchmark;
  389.   $rows=$estimated=$count=0;
  390.   for ($i=0 ; $i < $opt_medium_loop_count ; $i++)
  391.   {
  392.     $count++;
  393.     $rows+=fetch_all_rows($dbh,"select grp,count(distinct rev_idn) from bench1 group by grp");
  394.     $end_time=new Benchmark;
  395.     last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i+1,
  396.                        $opt_medium_loop_count));
  397.   }
  398.   print_time($estimated);
  399.   print " for count_distinct_group ($count:$rows): " .
  400.     timestr(timediff($end_time, $loop_time),"all") . "\n";
  401.  
  402.   $loop_time=new Benchmark;
  403.   $rows=$estimated=$count=0;
  404.   $test_count=$opt_medium_loop_count/10;
  405.   for ($i=0 ; $i < $test_count ; $i++)
  406.   {
  407.     $count++;
  408.     $rows+=fetch_all_rows($dbh,"select idn,count(distinct region) from bench1 group by idn");
  409.     $end_time=new Benchmark;
  410.     last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i+1,
  411.                        $test_count));
  412.   }
  413.   print_time($estimated);
  414.   print " for count_distinct_big ($count:$rows): " .
  415.     timestr(timediff($end_time, $loop_time),"all") . "\n";
  416. }
  417.  
  418. ####
  419. #### End of benchmark
  420. ####
  421.  
  422. if ($opt_lock_tables)
  423. {
  424.   do_query($dbh,"UNLOCK TABLES");
  425. }
  426. if (!$opt_skip_delete)
  427. {
  428.   do_query($dbh,"drop table bench1" . $server->{'drop_attr'});
  429. }
  430.  
  431. if ($opt_fast && defined($server->{vacuum}))
  432. {
  433.   $server->vacuum(0,\$dbh);
  434. }
  435.  
  436. $dbh->disconnect;                # close connection
  437.  
  438. end_benchmark($start_time);
  439.